"""
Case Type   : 功能测试 -- gsql执行统计时间测试
Case Name   : 执行sqlbypass SQL,查看db_tim和子时间的误差在10%以内
Create At   : 2023-12-11
Owner       : kyeleze
Description :
    1. 执行sqlbypass SQL
    2. 查看每个sql的db_tim和子时间的误差
Expect      :
    1. 执行成功
    2. 误差在10%以内
History     :
"""
import os
import unittest

from yat.test import Node

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

PrimarySh = CommonSH('PrimaryDbUser')


@unittest.skipIf(1 == PrimarySh.get_node_num(), '非主备环境不执行')
class DBTimeCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.user_node = Node('PrimaryDbUser')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.table_name = 't_dbtime_test_038'
        self.index_name = 'i_dbtime_test_038'
        self.user_param = f'-U {self.user_node.db_user} ' \
                          f'-W {self.user_node.db_password}'
        step = '----清理统计信息----'
        self.log.info(step)
        clean_sql = "select * from reset_unique_sql('global', 'all', 0);"
        result = self.primary_sh.execut_db_sql(clean_sql,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn('t', result.strip().splitlines()[2], '执行失败' + step)

    def test_sql_dbtime(self):
        step = '----step1:执行sqlbypass SQL expect:执行成功----'
        self.log.info(step)
        ddl_sql = f"""drop table if exists {self.table_name};
            create table {self.table_name}(id_int int, id_tinyint tinyint, 
            binary_int binary_integer, money_val money, bool_val boolean, 
            blob_val blob,"raw_val" raw, bytea bytea, cidr_addr cidr, 
            bit_val bit(3), json_val json, oid_val oid);
            create index {self.index_name} on {self.table_name}(id_int);
            insert into {self.table_name} values(generate_series(2,100), 255, 
            -3, +123, null, empty_blob(), 'DEADBEEF', e'xDEADBEEF', 
            '192.168/25', b'101', 
            '{{{{\\\"f1\\\":1,\\\"f2\\\":true,\\\"f3\\\":\\\"hi\\\"}}}}', 
            564182);"""
        result = self.primary_sh.execut_db_sql(ddl_sql,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, result,
                      '执行失败' + step)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, result,
                      '执行失败' + step)
        self.log.info('--insert by pass sql--')
        insert_cmd = f"""set enable_opfusion to on;set enable_bitmapscan=off;set 
            enable_seqscan=off;set opfusion_debug_mode = 'log';
            explain insert into {self.table_name} values(1, 
            255, -3, +123, null, empty_blob(), 'DEADBEEF', 
            E'xDEADBEEF', '192.168/25', B'101', 
            '{{{{\\\"f1\\\":1,\\\"f2\\\":true,\\\"f3\\\":\\\"Hi\\\"}}}}', 
            564182);
            insert into {self.table_name} values(1, 255, -3, 
            +123, null, empty_blob(), 'DEADBEEF', E'xDEADBEEF', 
            '192.168/25', B'101', 
            '{{{{\\\"f1\\\":1,\\\"f2\\\":true,\\\"f3\\\":\\\"Hi\\\"}}}}',
            564182);"""
        result = self.primary_sh.execut_db_sql(insert_cmd,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn("Bypass", result, '执行失败' + step)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, result,
                      '执行失败' + step)
        self.log.info('--update by pass sql--')
        update_cmd = f"set enable_opfusion to on;set enable_bitmapscan=off;" \
                     f"set enable_seqscan=off;set opfusion_debug_mode='log';" \
                     f"explain update {self.table_name} set binary_int=10 " \
                     f"where id_int=5;" \
                     f"update {self.table_name} set binary_int=10 " \
                     f"where id_int=5;"
        result = self.primary_sh.execut_db_sql(update_cmd,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn("Bypass", result, '执行失败' + step)
        self.assertIn(self.constant.UPDATE_SUCCESS_MSG, result,
                      '执行失败' + step)
        self.log.info('--delete by pass sql--')
        delete_cmd = f"set enable_opfusion to on;set enable_bitmapscan=off;" \
                     f"set enable_seqscan=off;set opfusion_debug_mode='log';" \
                     f"explain delete from {self.table_name} where id_int=10;" \
                     f"delete from {self.table_name} where id_int=10;"
        result = self.primary_sh.execut_db_sql(delete_cmd,
                                               sql_type=self.user_param)
        self.assertIn("Bypass", result, '执行失败' + step)
        self.assertIn(self.constant.DELETE_SUCCESS_MSG, result,
                      '执行失败' + step)
        self.log.info("--select by pass sql--")
        sele_cmd = f"set enable_opfusion to on;set enable_bitmapscan=off;" \
                   f"set enable_seqscan=off;set opfusion_debug_mode = 'log';" \
                   f"explain select * from {self.table_name} where id_int=3;" \
                   f"select * from {self.table_name} where id_int=3;"
        result = self.primary_sh.execut_db_sql(sele_cmd,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn("Bypass", result, '执行失败' + step)
        self.assertIn("1 row", result, '执行失败' + step)

        step = '--step2:查看每个sql的db_tim和子时间的误差 expect:误差在10%以内--'
        self.log.info(step)
        sql_list = [f'explain insert into {self.table_name}',
                    f'explain update {self.table_name}',
                    f'update {self.table_name}',
                    f'explain delete from {self.table_name}',
                    f'delete from {self.table_name}',
                    f'explain select * from {self.table_name}',
                    f'select * from {self.table_name}']
        for sql in sql_list:
            check_sql = f"select (select round((db_time-total_time)/db_time," \
                        f"2) from (select db_time, sum(execution_time + " \
                        f"parse_time + plan_time + rewrite_time + " \
                        f"pl_execution_time + pl_compilation_time + " \
                        f"data_io_time + net_send_time + srt1_q + " \
                        f"srt2_simple_query + srt3_analyze_rewrite + " \
                        f"srt4_plan_query + srt5_light_query + srt6_p + " \
                        f"srt7_b + srt8_e + srt9_d + srt10_s + srt11_c + " \
                        f"srt12_u + srt13_before_query + srt14_after_query) " \
                        f"as total_time from get_instr_unique_sql() where " \
                        f"query like '{sql}%' group by db_time)) < 0.1 as res;"
            result = self.primary_sh.execut_db_sql(check_sql,
                                                   sql_type=self.user_param)
            self.log.info(result)
            self.assertIn('', result.strip().splitlines()[2],
                          '执行失败' + step)

    def tearDown(self):
        step = '----清理环境----'
        self.log.info(step)
        clean_sql = f"drop table {self.table_name};"
        result = self.primary_sh.execut_db_sql(clean_sql,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, result,
                      '执行失败' + step)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
